package com.hanma.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hanma.bean.Category;
import com.hanma.bean.User;
import com.hanma.dao.CategoryDao;
import com.hanma.util.DBUtil;

public class CategoryDaoImpl implements CategoryDao{

	@Override
	public int insert(Category category) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="insert into category(name,pid)  values(?,?)"; 
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, category.getName());
			ps.setInt(2, category.getPid());
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int update(Category category) {
		System.out.println(category);
		Connection conn = DBUtil.open();
		int num=0;
		String sql="update category set name=?,pid=? where id=?"; 
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, category.getName());
			ps.setInt(2, category.getPid());
			ps.setInt(3, category.getId());
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int delete(Integer id) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="update category set status=0 where id=?"; 
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public Category find(Integer id) {
		Connection conn = DBUtil.open();
		String sql="select * from category where id=?"; 
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name=rs.getString("name");
				Integer pid=rs.getInt("pid");
				Integer status=rs.getInt("status");
				Category category = new Category(id,name,pid,status);
				return category;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return null;
	}

	@Override
	public List<Category> findAll(Integer pid) {
		Connection conn = DBUtil.open();
		String sql="select * from category where pid=? && status=1"; 
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Category> cateList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pid);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name=rs.getString("name");
				Integer id=rs.getInt("id");
				Integer status=rs.getInt("status");
				Category category = new Category(id,name,pid,status);
				cateList.add(category);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return cateList;
	}

	@Override
	public List<Category> findAllRec() {
		Connection conn = DBUtil.open();
		String sql="select * from category where recommend = 1 && status=1";
		Statement str = null;
		ResultSet rs = null;
		List<Category> cateList = new ArrayList<>();
		try {
			str = conn.createStatement();
			rs = str.executeQuery(sql);

			while(rs.next()) {
				Integer id=rs.getInt("id");
				String name=rs.getString("name");
				Integer pid=rs.getInt("pid");
				Integer status=rs.getInt("status");
				Category category = new Category(id,name,pid,status);
				cateList.add(category);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, str, conn);
		}
		return cateList;
	}

	@Override
	public List<Category> findAllByState() {
		Connection conn = DBUtil.open();
		String sql="select * from user where status = 1 order by id desc"; 
		Statement str = null;
		ResultSet rs = null;
		List<Category> cateList = new ArrayList<>();
		try {
			str = conn.createStatement();
		    rs = str.executeQuery(sql);
			
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String name=rs.getString("name");
				Integer pid=rs.getInt("pid");
				Integer status=rs.getInt("status");
				Category category = new Category(id,name,pid,status);
				cateList.add(category);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, str, conn);
		}
		return cateList;
	}

}
